import sqlite3
import json
import paho.mqtt.client as mqtt
from util import dict_factory
from sensor_command import SensorCommandGenerator
from util import parse_command_to_ascII
from db_function import DataBaseFunction

from setting import *

class DownlinksTask:

    def __init__(self) -> None:

        logger.info("~~~~~~~~~ DOWNLINK TASK START ~~~~~~~~~")

        self.sensor_command_generator = SensorCommandGenerator()

        # 获取tasks表中状态wait的任务列表

        with DataBaseFunction() as db:
            center_task_list = db.selete_tasks_by_status("wait")

        logger.info("Obtain the task list with the status' wait 'in the tasks table:")

        logger.info(center_task_list)

        for task in center_task_list:

            # 分解任务并插入到downlink_tasks任务表中
            self.downlinks_task_generator(json.loads(task["data_json"]))

        logger.info("~~~~~~~~~ DOWNLINK TASK STOP ~~~~~~~~~")
        logger.info("\n\n\n")

    def send_data_to_mqtt(self, data, topic):

        '''发送数据到mqtt
        data :{
                    "data": command,
                    "devaddr": devaddr,
                    "port": 12
                }
        '''
        logger.info(f"send to baseStaion data: {topic}")
        logger.info(data)
        logger.info("")
        # 连接 MQTT 代理
        client = mqtt.Client()
        # client.connect("mqtt_broker_address", 1883)  # 替换为您的 MQTT 代理地址和端口

        # 建立连接
        client.connect(MQTT_LOCAL_HOST, MQTT_LOCAL_PORT, 60)


        payload = json.dumps(data)

        # 发布数据到指定主题
        client.publish(topic, payload)

        # 断开 MQTT 连接
        client.disconnect()

            
    @staticmethod
    def select_sensor_by_mac(sensor_mac):
        '''插入传感器验证一下表中是否能插入'''

        sql = "select * from sensors where mac=? and status is null"

        try:
            conn = sqlite3.connect('sqlite3.db')

            # 创建一个游标对象
            cursor = conn.cursor()

            # 执行插入语句
            cursor.execute(sql, (sensor_mac, ))

        except Exception as e:

            logger.error(f"set_tasks_table_status: {e.args}")

            return None

        else:

            result = cursor.fetchone()

            if result:

                return result
            else:

                return None
            
        finally:

            cursor.close()

            conn.close()

    def downlinks_task_insert_to_db(self, base_station_mac, sensor_mac, data_json, sn, task_id, status="execute"):

        """downlinks任务保存到数据库"""

        # SQL 插入语句
        sql = '''INSERT INTO downlink_tasks (base_station_mac, sensor_mac, data_json, sn, task_id, status) VALUES (?, ?, ?, ?, ?, ?)'''

        try:
            conn = sqlite3.connect('sqlite3.db')

            # 创建一个游标对象
            cursor = conn.cursor()

            # 执行插入语句
            cursor.execute(sql, (base_station_mac, sensor_mac, data_json, sn, task_id, status))

            # 获取插入的 ID
            task_id = cursor.lastrowid

        except Exception as e:

            logger.error(f"downlinks任务保存到数据库时失败： {e.args}")

            return 0

        else:

            # 提交事务
            conn.commit()

            return task_id

        finally:

            # 关闭游标
            cursor.close()

            # 关闭连接
            conn.close()


    def downlinks_task_generator_by_senor_set(self, task): 
        '''只能处理开头是set任务 
        
        task: {'task_type': 'set_sensor_light', 'task_id': '3', 'data': {'devaddr_list': ['传感器1_MAC地址'], 'value': '00'}}'''

        if not task["task_type"].startswith("set"):

            logger.error(f"downlinks_task_generator_by_senor_set: 只能处理开头是set任务")

            return 

        task_id = task["task_id"]
        sn = int(task["task_id"]) % 255
        task_type = task["task_type"]
        devaddr_list = task['data'].get("devaddr_list")

        for devaddr in devaddr_list:

            command_generator_fun = self.sensor_command_generator.get_function_object(task_type)

            command = parse_command_to_ascII(command_generator_fun(int(task['data']['value'], 16), sn))

            logger.info(command)

            downlinks_data = {
                "data": command,
                "devaddr": devaddr,
                "port": 12
            }

            with DataBaseFunction() as db:

                base_station_mac = db.select_base_station_mac_by_sensor_mac(devaddr)

            self.downlinks_task_insert_to_db(base_station_mac, devaddr, json.dumps(downlinks_data), sn, task_id, status="execute")

            self.send_data_to_mqtt(downlinks_data, base_station_mac) # TODO 发送可做优化

        with DataBaseFunction() as db:

            db.update_tasks_status(task_id=task_id, status="execute")


    def downlinks_task_generator_by_senor_read(self, task): 
        '''只能处理开头是read任务
        task: {"task_type": "read_reporting_cycle",	 "task_id": "3","data": {"devaddr_list": ["917B1D71", "9759A47A"],}}
        '''

        if not task["task_type"].startswith("read"):

            logger.error(f"downlinks_task_generator_by_senor_read: 只能处理开头是read任务")

            return 

        task_id = task["task_id"]
        sn = int(task["task_id"]) % 255
        task_type = task["task_type"]
        devaddr_list = task['data'].get("devaddr_list")

        for devaddr in devaddr_list:

            command_generator_fun = self.sensor_command_generator.get_function_object(task_type)

            command = parse_command_to_ascII(command_generator_fun(sn))

            logger.info(command)

            downlinks_data = {
                "data": command,
                "devaddr": devaddr,
                "port": 12
            }

            with DataBaseFunction() as db:

                base_station_mac = db.select_base_station_mac_by_sensor_mac(devaddr)

            self.downlinks_task_insert_to_db(base_station_mac, devaddr, json.dumps(downlinks_data), sn, task_id, status="execute")

            self.send_data_to_mqtt(downlinks_data, base_station_mac) # TODO 发送可做优化


    def downlinks_task_generator_by_gateways_upgrade(self, task):
        '''基站升级
        {"task_type": "gateways_upgrade", "task_id": "2", "data": {"file_url": "http://192.168.88.34:33332/", "gateways_list": ["917B1D71", "9759A47A"]	}}'''

        if task["task_type"] != "gateways_upgrade":

            logger.error(f"downlinks_task_generator_by_gateways_upgrade: 只能处理gateways_upgrade任务")

            return
        
        task_id = task["task_id"]
        sn = int(task["task_id"]) % 255
        gateways_list = task['data'][gateways_list]

        for base_station_mac in gateways_list:

            downlink_task_id = self.downlinks_task_insert_to_db(base_station_mac, "基站升级", json.dumps(task), sn, task_id, status="execute")
            
            task["downlink_task_id"] = downlink_task_id # task 加上一个 downlink_task_id字段直接下发

            self.send_data_to_mqtt(task, f"{base_station_mac}_transcations") # TODO 发送可做优化 base_station_transactions 这里topic是基站专门的通道


    def downlinks_task_generator_by_sensor_upgrade(self, task):

        '''传感器升级
        {"task_type": "sensor_upgrade", "task_id": "3", "data": {"file_url": "http://192.168.88.34:33332/","devaddr_list": ["917B1D71", "9759A47A"]}}
        '''

        if task["task_type"] != "sensor_upgrade":

            logger.error(f"downlinks_task_generator_by_sensor_upgrade: 只能处理sensor_upgrade任务")

            return 
        
        task_id = task["task_id"]
        sn = int(task["task_id"]) % 255
        devaddr_list = task['data'].get("devaddr_list")

        for devaddr in devaddr_list:

            with DataBaseFunction() as db:

                base_station_mac = db.select_base_station_mac_by_sensor_mac(devaddr)

            self.downlinks_task_insert_to_db(base_station_mac, devaddr, json.dumps(task), sn, task_id, status="wait") # 传感器升级下发在任务检测模块中进行下发

        

                
            
    def downlinks_task_generator_by_gateways_info(self, task):
        '''基站信息提交
        {"task_type": "gateways_info", "task_id": "333", "data": {"gateway_mac": "E45F01FFFE422525"}}
        '''
        if task["task_type"] != "gateways_info":

            logger.error(f"downlinks_task_generator_by_gateways_info: 只能处理gateways_info任务")

            return
        
        task_id = task["task_id"]
        sn = int(task["task_id"]) % 255
        gateways_list = task['data'][gateways_list]

        for base_station_mac in gateways_list:

            downlink_task_id = self.downlinks_task_insert_to_db(base_station_mac, "基站提交信息", json.dumps(task), sn, task_id, status="execute")

            task["downlink_task_id"] = downlink_task_id # task 加上一个 downlink_task_id字段直接下发

            self.send_data_to_mqtt(task, f"{base_station_mac}_transcations") # TODO 发送可做优化 base_station_transactions 这里topic是基站专门的通道

    def downlinks_task_generator_by_sensor_delete(self, task):
        '''传感器删除
        {"task_type": "sensor_delete", "task_id": "3", "data": {"devaddr_list": ["917B1D71", "9759A47A"] }}
        '''
        # SQL 插入语句
        sql = "UPDATE sensors SET status = 'delete' WHERE mac = ?;"

        task_status_sql = "update tasks set status=? where task_id=?"

        mac_list = [(mac,) for mac in task["data"]["devaddr_list"]]

        try:
            conn = sqlite3.connect('sqlite3.db')

            # 创建一个游标对象
            cursor = conn.cursor()

            # 执行插入语句
            cursor.executemany(sql, mac_list)
            
        except Exception as e:

            logger.error(f"接收心任务传感器删除操作数据库时失败： {e.args}")

            cursor.execute(task_status_sql, ("fail", task["task_id"]))

            return 0

        else:

            cursor.execute(task_status_sql, ("success", task["task_id"])) # 成功后更该tasks表的对应的任务状态为success

            # 提交事务
            conn.commit()

            return 1

        finally:

            # 关闭游标
            cursor.close()

            # 关闭连接
            conn.close()


    def downlinks_task_generator_by_sensor_add(self, task):
        '''传感器增加
        {
            "task_type": "sensor_add", "task_id": "3", 
            "data": {
                "devaddr_list": [{"devaddr":"917B1D71", "sensor_type": "A", "brackets_id": 1}, {"devaddr":"917B1D72", "sensor_type": "B", "brackets_id": 1}], 
                "gateway_mac": "E45F01FFFE422525"
            }
        }
        '''
        # SQL 插入语句
        sql = "INSERT INTO sensors (mac, sensor_type, brackets_id) VALUES (?, ?, ?)"

        task_status_sql = "update tasks set status=? where task_id=?"

        devaddr_list = task['data']['devaddr_list']

        try:
            conn = sqlite3.connect('sqlite3.db')

            # 创建一个游标对象
            cursor = conn.cursor()

            for sensor_data in devaddr_list:

                if self.select_sensor_by_mac(sensor_data['devaddr']) ==None:
                    devaddr = sensor_data['devaddr']
                    sensor_type = sensor_data['sensor_type']
                    brackets_id = sensor_data['brackets_id']
                    cursor.execute(sql, (devaddr, sensor_type, brackets_id))
            
        except Exception as e:

            logger.error(f"接收心任务传感器删除操作数据库时失败： {e.args}")
            cursor.execute(task_status_sql, ("fail", task["task_id"])) # 失败后更该tasks表的对应的任务状态为fail

            return 0

        else:

            cursor.execute(task_status_sql, ("success", task["task_id"])) # 成功后更该tasks表的对应的任务状态为success
            # 提交事务
            conn.commit()

            return 1

        finally:

            # 关闭游标
            cursor.close()

            # 关闭连接
            conn.close()

    def downlinks_task_generator(self, task):
        '''downlinks任务生成器,主函数'''
        # base_station_mac, sensor_mac, data_json, sn, task_id, status="execute"

        task_type = task["task_type"]

        if task_type.startswith("set"):  # 任务类型为传感器数值设置的

            self.downlinks_task_generator_by_senor_set(task)

        elif task_type.startswith("read"): # 任务类型为读取传感器数值设置的

            self.downlinks_task_generator_by_senor_set(task)

        elif task_type == "gateways_upgrade":

            self.downlinks_task_generator_by_gateways_upgrade(task)

        elif task_type == "sensor_upgrade":

            self.downlinks_task_generator_by_sensor_upgrade(task)

        elif task_type == "gateways_info":

            self.downlinks_task_generator_by_gateways_info(task)

        elif task_type == "sensor_delete":

            self.downlinks_task_generator_by_sensor_delete(task)

        elif task_type == "sensor_add":

            self.downlinks_task_generator_by_sensor_add(task)



if __name__ == "__main__":

    from sentry import TaskSentry

    DownlinksTask()

